import ExcelJS from 'exceljs';
import { Disciple } from '../domain/Disciple';
import { Detail } from '../domain/Detail';
import { ScoringRule } from '../domain/ScoringRule';
import { DiscipleService } from '../service/DiscipleService';
import { RuleService } from '../service/RuleService';
import { ScoringPeriodService } from '../service/ScoringPeriodService';
import { ScoringPeriod } from '../domain/ScoringPeriod';

/**
 * Excel导出工具类
 * 用于导出弟子积分数据到Excel文件
 */
export class ExcelExportUtil {
  /**
   * 导出所有弟子数据到Excel
   * @param filename 文件名（可选）
   * @param period 计分周期（可选，默认当前周期）
   * @returns Promise<void>
   */
  static async exportToExcel(filename?: string, period?: ScoringPeriod | null): Promise<void> {
    const workbook = new ExcelJS.Workbook();
    const disciples = DiscipleService.getAllDisciple();
    const rules = RuleService.getAllRules();
    if (!period) {
      period = ScoringPeriodService.getCurrentPeriod();
    } 
    const currentPeriod = period;
    // 获取周期月份列表
    const months = this.getPeriodMonths(currentPeriod.startDate, currentPeriod.endDate);

    // 创建第一张表：总表
    const summarySheet = workbook.addWorksheet('总表');
    this.createSummarySheet(summarySheet, disciples, months, currentPeriod);

    // 为每个弟子创建一张表
    for (const disciple of disciples) {
      const sheetName = `${disciple.name}（${disciple.code}）`;
      // Excel工作表名称不能超过31个字符
      const validSheetName = sheetName.length > 31 ? sheetName.substring(0, 31) : sheetName;
      const discipleSheet = workbook.addWorksheet(validSheetName);
      this.createDiscipleSheet(discipleSheet, disciple, rules, months, currentPeriod);
    }

    // 生成文件名 todo: 小组问题
    const defaultFilename = `启宇国学弟子积分卡-${currentPeriod.name}-${new Date().toISOString().split('T')[0]}.xlsx`;
    const finalFilename = filename || defaultFilename;

    // 下载文件
    const buffer = await workbook.xlsx.writeBuffer();
    const blob = new Blob([buffer], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' });
    const url = URL.createObjectURL(blob);
    const a = document.createElement('a');
    a.href = url;
    a.download = finalFilename;
    document.body.appendChild(a);
    a.click();
    document.body.removeChild(a);
    URL.revokeObjectURL(url);
  }

  /**
   * 获取周期内的月份列表
   * @param startDate 开始日期
   * @param endDate 结束日期
   * @returns 月份列表，格式：['5月', '6月', ...]
   */
  private static getPeriodMonths(startDate: string, endDate: string): string[] {
    const months: string[] = [];
    const start = new Date(startDate);
    const end = new Date(endDate);
    
    let current = new Date(start.getFullYear(), start.getMonth(), 1);
    const endMonth = new Date(end.getFullYear(), end.getMonth(), 1);

    while (current <= endMonth) {
      const monthNum = current.getMonth() + 1;
      months.push(`${monthNum}月`);
      current.setMonth(current.getMonth() + 1);
    }

    return months;
  }

  /**
   * 创建总表
   * @param sheet 工作表
   * @param disciples 弟子列表
   * @param months 月份列表
   * @param period 当前周期
   */
  private static createSummarySheet(
    sheet: ExcelJS.Worksheet,
    disciples: Disciple[],
    months: string[],
    period: any
  ): void {
    // 设置列宽
    sheet.getColumn(1).width = 8; // 字号
    sheet.getColumn(2).width = 12; // 姓名
    sheet.getColumn(3).width = 12; // 赐号
    months.forEach((_, index) => {
      sheet.getColumn(4 + index).width = 10; // 各月得分
    });
    const monthColCount = months.length;
    sheet.getColumn(4 + monthColCount).width = 12; // 总分
    sheet.getColumn(5 + monthColCount).width = 12; // 最后得分
    sheet.getColumn(6 + monthColCount).width = 15; // 师门得分合计
    sheet.getColumn(7 + monthColCount).width = 15; // 组长加分合计
    sheet.getColumn(8 + monthColCount).width = 10; // 扣分
    sheet.getColumn(9 + monthColCount).width = 15; // 本周期星级情况
    sheet.getColumn(10 + monthColCount).width = 20; // 备注

    // 创建标题行
    const titleRow = sheet.addRow(['诚正组弟子门人修身学习积分卡——总表']);
    titleRow.font = { size: 16, bold: true };
    titleRow.alignment = { horizontal: 'center', vertical: 'middle' };
    sheet.mergeCells(1, 1, 1, 3 + months.length + 7);

    // 创建表头
    const headerRow = sheet.addRow([
      '字号',
      '姓名',
      '赐号',
      ...months,
      '总分',
      '最后得分',
      '师门得分合计',
      '组长加分合计',
      '扣分',
      '本周期星级情况',
      '备注'
    ]);

    // 设置表头样式
    headerRow.eachCell((cell, colNumber) => {
      cell.font = { bold: true, size: 11 };
      cell.alignment = { horizontal: 'center', vertical: 'middle', wrapText: true };
      cell.fill = {
        type: 'pattern',
        pattern: 'solid',
        fgColor: { argb: 'FFE6F3FF' } // 浅蓝色背景
      };
      cell.border = {
        top: { style: 'thin' },
        left: { style: 'thin' },
        bottom: { style: 'thin' },
        right: { style: 'thin' }
      };
    });

    // 设置月份列的背景色
    months.forEach((_, index) => {
      const cell = headerRow.getCell(4 + index);
      cell.fill = {
        type: 'pattern',
        pattern: 'solid',
        fgColor: { argb: 'FFB3D9FF' } // 更浅的蓝色
      };
    });

    // 设置总分列背景色（红色）
    const totalScoreCell = headerRow.getCell(4 + monthColCount);
    totalScoreCell.fill = {
      type: 'pattern',
      pattern: 'solid',
      fgColor: { argb: 'FFFFCCCC' } // 浅红色
    };

    // 设置最后得分列背景色（黄色）
    const finalScoreCell = headerRow.getCell(5 + monthColCount);
    finalScoreCell.fill = {
      type: 'pattern',
      pattern: 'solid',
      fgColor: { argb: 'FFFFFFCC' } // 浅黄色
    };

    // 设置师门得分合计列背景色（橙色）
    const masterScoreCell = headerRow.getCell(6 + monthColCount);
    masterScoreCell.fill = {
      type: 'pattern',
      pattern: 'solid',
      fgColor: { argb: 'FFFFE6CC' } // 浅橙色
    };

    // 设置组长加分合计列背景色（蓝色）
    const leaderScoreCell = headerRow.getCell(7 + monthColCount);
    leaderScoreCell.fill = {
      type: 'pattern',
      pattern: 'solid',
      fgColor: { argb: 'FFCCE6FF' } // 浅蓝色
    };

    // 设置扣分列背景色（紫色）
    const deductionCell = headerRow.getCell(8 + monthColCount);
    deductionCell.fill = {
      type: 'pattern',
      pattern: 'solid',
      fgColor: { argb: 'FFE6CCFF' } // 浅紫色
    };

    // 设置星级列背景色（绿色）
    const starCell = headerRow.getCell(9 + monthColCount);
    starCell.fill = {
      type: 'pattern',
      pattern: 'solid',
      fgColor: { argb: 'FFCCFFCC' } // 浅绿色
    };

    // 设置备注列背景色
    const remarkCell = headerRow.getCell(10 + monthColCount);
    remarkCell.fill = {
      type: 'pattern',
      pattern: 'solid',
      fgColor: { argb: 'FFB3D9FF' } // 浅蓝色
    };

    // 添加数据行
    disciples.forEach((disciple, index) => {
      const rowIndex = index + 3; // 第1行是标题，第2行是表头，数据从第3行开始
      const monthlyScores = this.getMonthlyScores(disciple, months, period);
      // 将0值转换为空字符串
      const monthlyScoresDisplay = monthlyScores.map(score => score === 0 ? '' : score);
      const finalScore = disciple.getCurrentPeriodCappedTotalScore();
      const masterScore = disciple.getSectBonusTotal();
      const leaderScore = disciple.getLeaderBonusTotal();
      const deduction = this.getDeductionScore(disciple, period);
      const star = disciple.star || 0;

      const dataRow = sheet.addRow([
        index + 1, // 字号
        disciple.name,
        disciple.code,
        ...monthlyScoresDisplay,
        null, // 总分，使用公式计算
        finalScore === 0 ? '' : finalScore,
        masterScore === 0 ? '' : masterScore,
        leaderScore === 0 ? '' : leaderScore,
        deduction === 0 ? '' : deduction,
        star === 0 ? '' : `${star}星`,
        '' // 备注
      ]);

      // 设置数据行样式
      dataRow.eachCell((cell, colNumber) => {
        cell.alignment = { horizontal: 'center', vertical: 'middle' };
        cell.border = {
          top: { style: 'thin' },
          left: { style: 'thin' },
          bottom: { style: 'thin' },
          right: { style: 'thin' }
        };

        // 姓名和赐号列使用黄色背景
        if (colNumber === 2 || colNumber === 3) {
          cell.fill = {
            type: 'pattern',
            pattern: 'solid',
            fgColor: { argb: 'FFFFFFCC' } // 浅黄色
          };
        }

        // 数值列设置数字格式
        if (colNumber >= 4 && colNumber <= 4 + monthColCount) {
          cell.numFmt = '0.0';
        }
        if (colNumber >= 4 + monthColCount && colNumber <= 8 + monthColCount) {
          cell.numFmt = '0.0';
        }
      });

      // 设置总分列使用SUM公式，如果结果为0则显示为空
      const totalScoreDataCell = dataRow.getCell(4 + monthColCount);
      const firstMonthCol = this.getExcelColumnLetter(4);
      const lastMonthCol = this.getExcelColumnLetter(3 + monthColCount);
      totalScoreDataCell.value = {
        formula: `IF(SUM(${firstMonthCol}${rowIndex}:${lastMonthCol}${rowIndex})=0,"",SUM(${firstMonthCol}${rowIndex}:${lastMonthCol}${rowIndex}))`
      };
      totalScoreDataCell.numFmt = '0.0';
      totalScoreDataCell.fill = {
        type: 'pattern',
        pattern: 'solid',
        fgColor: { argb: 'FFFFCCCC' } // 浅红色
      };

      // 设置最后得分列背景色
      const finalScoreDataCell = dataRow.getCell(5 + monthColCount);
      finalScoreDataCell.fill = {
        type: 'pattern',
        pattern: 'solid',
        fgColor: { argb: 'FFFFFFCC' } // 浅黄色
      };

      // 设置师门得分合计列背景色
      const masterScoreDataCell = dataRow.getCell(6 + monthColCount);
      masterScoreDataCell.fill = {
        type: 'pattern',
        pattern: 'solid',
        fgColor: { argb: 'FFFFE6CC' } // 浅橙色
      };

      // 设置组长加分合计列背景色
      const leaderScoreDataCell = dataRow.getCell(7 + monthColCount);
      leaderScoreDataCell.fill = {
        type: 'pattern',
        pattern: 'solid',
        fgColor: { argb: 'FFCCE6FF' } // 浅蓝色
      };

      // 设置扣分列背景色
      const deductionDataCell = dataRow.getCell(8 + monthColCount);
      deductionDataCell.fill = {
        type: 'pattern',
        pattern: 'solid',
        fgColor: { argb: 'FFE6CCFF' } // 浅紫色
      };

      // 设置星级列背景色
      const starDataCell = dataRow.getCell(9 + monthColCount);
      starDataCell.fill = {
        type: 'pattern',
        pattern: 'solid',
        fgColor: { argb: 'FFCCFFCC' } // 浅绿色
      };
    });
  }

  /**
   * 创建弟子个人表
   * @param sheet 工作表
   * @param disciple 弟子对象
   * @param rules 规则列表
   * @param months 月份列表
   * @param period 当前周期
   */
  private static createDiscipleSheet(
    sheet: ExcelJS.Worksheet,
    disciple: Disciple,
    rules: ScoringRule[],
    months: string[],
    period: any
  ): void {
    // 设置列宽
    sheet.getColumn(1).width = 30; // 内容
    sheet.getColumn(2).width = 8; // 月
    months.forEach((_, index) => {
      sheet.getColumn(3 + index).width = 10; // 各月得分
    });
    const monthColCount = months.length;
    sheet.getColumn(3 + monthColCount).width = 12; // 总分合计
    sheet.getColumn(4 + monthColCount).width = 15; // 师门得分合计
    sheet.getColumn(5 + monthColCount).width = 15; // 组长加分合计

    // 创建标题行 todo: 小组问题
    const titleRow = sheet.addRow([`启予国学弟子积分卡`]);
    titleRow.font = { size: 14, bold: true };
    titleRow.alignment = { horizontal: 'center', vertical: 'middle' };
    sheet.mergeCells(1, 1, 1, 2 + months.length + 3);

    // 创建表头
    const headerRow = sheet.addRow([
      '内容',
      '月',
      ...months,
      '总分合计',
      '师门得分合计',
      '组长加分合计'
    ]);

    // 设置表头样式
    headerRow.eachCell((cell, colNumber) => {
      cell.font = { bold: true, size: 11 };
      cell.alignment = { horizontal: 'center', vertical: 'middle', wrapText: true };
      cell.fill = {
        type: 'pattern',
        pattern: 'solid',
        fgColor: { argb: 'FFE6F3FF' } // 浅蓝色背景
      };
      cell.border = {
        top: { style: 'thin' },
        left: { style: 'thin' },
        bottom: { style: 'thin' },
        right: { style: 'thin' }
      };
    });

    // 添加规则数据行
    rules.forEach((rule, ruleIndex) => {
      const rowIndex = ruleIndex + 3; // 第1行是标题，第2行是表头，数据从第3行开始
      const monthlyScores = this.getRuleMonthlyScores(disciple, rule, months, period);
      // 将0值转换为空字符串
      const monthlyScoresDisplay = monthlyScores.map(score => score === 0 ? '' : score);
      const { masterTotal, leaderTotal } = this.getRuleTotals(disciple, rule, period);

      const dataRow = sheet.addRow([
        rule.item, // 内容
        '', // 月（空）
        ...monthlyScoresDisplay,
        null, // 总分合计，使用公式计算
        masterTotal === 0 ? '' : masterTotal,
        leaderTotal === 0 ? '' : leaderTotal
      ]);

      // 设置数据行样式
      dataRow.eachCell((cell, colNumber) => {
        cell.alignment = { 
          horizontal: colNumber === 1 ? 'left' : 'center', 
          vertical: 'middle' 
        };
        cell.border = {
          top: { style: 'thin' },
          left: { style: 'thin' },
          bottom: { style: 'thin' },
          right: { style: 'thin' }
        };

        // 数值列设置数字格式
        if (colNumber >= 3 && colNumber <= 2 + monthColCount) {
          cell.numFmt = '0.0';
        }
        if (colNumber >= 3 + monthColCount && colNumber <= 5 + monthColCount) {
          cell.numFmt = '0.0';
        }
      });

      // 设置总分合计列使用SUM公式，如果结果为0则显示为空
      const totalScoreCell = dataRow.getCell(3 + monthColCount);
      const firstMonthCol = this.getExcelColumnLetter(3);
      const lastMonthCol = this.getExcelColumnLetter(2 + monthColCount);
      totalScoreCell.value = {
        formula: `IF(SUM(${firstMonthCol}${rowIndex}:${lastMonthCol}${rowIndex})=0,"",SUM(${firstMonthCol}${rowIndex}:${lastMonthCol}${rowIndex}))`
      };
      totalScoreCell.numFmt = '0.0';
    });

    // 添加汇总行（师门得分合计和组长加分合计）
    const summaryRowIndex = rules.length + 3; // 规则行数 + 标题行 + 表头行
    const masterTotal = disciple.getSectBonusTotal();
    const leaderTotal = disciple.getLeaderBonusTotal();
    const summaryRow = sheet.addRow([
      '汇总',
      '',
      ...new Array(monthColCount).fill(''),
      null, // 总分合计，使用公式计算
      masterTotal === 0 ? '' : masterTotal,
      leaderTotal === 0 ? '' : leaderTotal
    ]);

    summaryRow.eachCell((cell, colNumber) => {
      cell.font = { bold: true };
      cell.alignment = { 
        horizontal: colNumber === 1 ? 'left' : 'center', 
        vertical: 'middle' 
      };
      cell.border = {
        top: { style: 'thin' },
        left: { style: 'thin' },
        bottom: { style: 'thin' },
        right: { style: 'thin' }
      };
      if (colNumber >= 3 + monthColCount && colNumber <= 5 + monthColCount) {
        cell.numFmt = '0.0';
      }
    });

    // 设置汇总行的总分合计列使用SUM公式（汇总所有规则的总分合计），如果结果为0则显示为空
    const summaryTotalScoreCell = summaryRow.getCell(3 + monthColCount);
    const firstRuleRow = 3;
    const lastRuleRow = rules.length + 2;
    const totalScoreCol = this.getExcelColumnLetter(3 + monthColCount);
    summaryTotalScoreCell.value = {
      formula: `IF(SUM(${totalScoreCol}${firstRuleRow}:${totalScoreCol}${lastRuleRow})=0,"",SUM(${totalScoreCol}${firstRuleRow}:${totalScoreCol}${lastRuleRow}))`
    };
    summaryTotalScoreCell.numFmt = '0.0';
  }

  /**
   * 获取弟子各月得分
   * @param disciple 弟子对象
   * @param months 月份列表
   * @param period 当前周期
   * @returns 各月得分数组
   */
  private static getMonthlyScores(
    disciple: Disciple,
    months: string[],
    period: any
  ): number[] {
    const scores: number[] = [];

    months.forEach(monthStr => {
      const monthNum = parseInt(monthStr.replace('月', ''));
      const startYear = new Date(period.startDate).getFullYear();
      const startMonth = new Date(period.startDate).getMonth() + 1; // getMonth()返回0-11，需要+1
      
      // 处理跨年的情况（11-4月周期）
      let actualYear = startYear;
      if (monthNum >= 11) {
        actualYear = startYear;
      } else if (monthNum <= 4 && startMonth >= 11) {
        actualYear = startYear + 1;
      }

      const startDate = `${actualYear}-${String(monthNum).padStart(2, '0')}-01`;
      // 获取该月的最后一天
      const lastDay = new Date(actualYear, monthNum, 0).getDate();
      const endDate = `${actualYear}-${String(monthNum).padStart(2, '0')}-${String(lastDay).padStart(2, '0')}`;

      const monthDetails = disciple.details.filter(detail => {
        const detailDate = detail.date;
        return detailDate >= startDate && detailDate <= endDate;
      });

      const monthScore = monthDetails.reduce(
        (sum, detail) => sum + detail.leaderScore + detail.masterScore,
        0
      );

      scores.push(monthScore);
    });

    return scores;
  }

  /**
   * 获取规则在各月的得分
   * @param disciple 弟子对象
   * @param rule 规则对象
   * @param months 月份列表
   * @param period 当前周期
   * @returns 各月得分数组
   */
  private static getRuleMonthlyScores(
    disciple: Disciple,
    rule: ScoringRule,
    months: string[],
    period: any
  ): number[] {
    const scores: number[] = [];

    months.forEach(monthStr => {
      const monthNum = parseInt(monthStr.replace('月', ''));
      const startYear = new Date(period.startDate).getFullYear();
      const startMonth = new Date(period.startDate).getMonth() + 1; // getMonth()返回0-11，需要+1
      
      // 处理跨年的情况（11-4月周期）
      let actualYear = startYear;
      if (monthNum >= 11) {
        actualYear = startYear;
      } else if (monthNum <= 4 && startMonth >= 11) {
        actualYear = startYear + 1;
      }

      const startDate = `${actualYear}-${String(monthNum).padStart(2, '0')}-01`;
      // 获取该月的最后一天
      const lastDay = new Date(actualYear, monthNum, 0).getDate();
      const endDate = `${actualYear}-${String(monthNum).padStart(2, '0')}-${String(lastDay).padStart(2, '0')}`;

      const monthDetails = disciple.details.filter(detail => {
        const detailDate = detail.date;
        return (
          detail.rule.id === rule.id &&
          detailDate >= startDate &&
          detailDate <= endDate
        );
      });

      const monthScore = monthDetails.reduce(
        (sum, detail) => sum + detail.leaderScore + detail.masterScore,
        0
      );

      scores.push(monthScore);
    });

    return scores;
  }

  /**
   * 获取规则的师门得分合计和组长加分合计
   * @param disciple 弟子对象
   * @param rule 规则对象
   * @param period 当前周期
   * @returns 包含师门得分合计和组长加分合计的对象
   */
  private static getRuleTotals(
    disciple: Disciple,
    rule: ScoringRule,
    period: any
  ): { masterTotal: number; leaderTotal: number } {
    const periodDetails = disciple.details.filter(detail => {
      return detail.rule.id === rule.id &&
        detail.date >= period.startDate &&
        detail.date <= period.endDate;
    });

    const masterTotal = periodDetails.reduce(
      (sum, detail) => sum + detail.masterScore,
      0
    );

    const leaderTotal = periodDetails.reduce(
      (sum, detail) => sum + detail.leaderScore,
      0
    );

    return { masterTotal, leaderTotal };
  }

  /**
   * 获取扣分（负分合计）
   * @param disciple 弟子对象
   * @param period 当前周期
   * @returns 扣分合计
   */
  private static getDeductionScore(disciple: Disciple, period: any): number {
    const periodDetails = disciple.details.filter(detail => {
      return detail.date >= period.startDate && detail.date <= period.endDate;
    });

    const deduction = periodDetails.reduce((sum, detail) => {
      const total = detail.leaderScore + detail.masterScore;
      return total < 0 ? sum + Math.abs(total) : sum;
    }, 0);

    return deduction;
  }

  /**
   * 将列号转换为Excel列字母（1 -> A, 2 -> B, ..., 27 -> AA, ...）
   * @param colNumber 列号（从1开始）
   * @returns Excel列字母
   */
  private static getExcelColumnLetter(colNumber: number): string {
    let result = '';
    while (colNumber > 0) {
      colNumber--;
      result = String.fromCharCode(65 + (colNumber % 26)) + result;
      colNumber = Math.floor(colNumber / 26);
    }
    return result;
  }
}

